package DBLayer;

import ModelLayer.*;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

public class DBPartialAssignment {

    private Connection con;

    // Constructor
    public DBPartialAssignment(){
        con = DbConnection1.getInstance().getDBcon();
    }

    public ArrayList<PartialAssignment> getAllPartialAssignments()
    {
        return miscWhere("");
    }

    public PartialAssignment findPartialAssignmentId(String idPartialAssignment)
    {   String wClause = " idPartialAssignment = '" + idPartialAssignment + "'";
        System.out.println("FindAssignmentId " +wClause);
        return singleWhere(wClause);
    }

    public PartialAssignment findPartialAssignmentName(String attValue)
    {   String wClause = "nameAssignment = '" + attValue + "'";
        System.out.println("FindAssignmentName " + wClause);
        return singleWhere(wClause);
    }


    public int insertPartialAssignment(PartialAssignment part) throws Exception
    {
        int nextIdPartialAssignment = GetMax.getMaxId("Select max(idPartialAssignment) from PartialAssignment");
        nextIdPartialAssignment = nextIdPartialAssignment + 1;
        System.out.println("next idPartialAssignment = " +  nextIdPartialAssignment);

        int rc = -1;
        String query="INSERT INTO PartialAssignment(idPartialAssignment, nameAssignment, " +
                "area, hourExpected, hourSpent, note, priceAssignment)  VALUES('"+
                nextIdPartialAssignment  + "','"  +
                //part.getMaterialList().get(1).getIdMaterial()  + "','"  +
                //part.getEmployee().getIdEmployee() + "','" +
                part.getNameAssignment() + "','" +
                part.getArea() + "','" +
                part.getHourExpected() + "','" +
                part.getHourSpent() + "','" +
                part.getNote() + "','" +
                part.getPriceAssignment() + "')";

        System.out.println("insert : " + query);
        try{
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            rc = stmt.executeUpdate(query);
            stmt.close();
        }
        catch(SQLException ex){
            System.out.println("PartialAssignment not created");
            throw new Exception ("PartialAssignment is not inserted");
        }
        return(rc);
    }

    // Update with name instead?
    public int updatePartialAssignment(PartialAssignment part)
    {
        int rc=-1;

        String query="UPDATE partialAssignment SET "+
                //"idMaterial ='"+ part.getMaterialList().get(1).  + "', " +
                //"idEmployee ='"+ part.getEmployee().getIdEmployee() + "', " +
                "nameAssignment ='"+ part.getNameAssignment() + "' " +
                "area ='"+ part.getArea() + "' " +
                "hourExpected ='"+ part.getHourExpected() + "' " +
                "hourSpent ='"+ part.getHourSpent() + "' " +
                "note ='"+ part.getNote() + "' " +
                "priceAssignment ='"+ part.getPriceAssignment() + "' " +
                "WHERE idPartialAssignment = '"+ part.getIdPartialAssignment() + "'";
        System.out.println("Update query:" + query);
        try{
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            rc = stmt.executeUpdate(query);

            stmt.close();
        }
        catch(Exception ex){
            System.out.println("Update exception in partialAssignment db: "+ex);
        }
        return(rc);
    }

    // Delete with name instead?
    public int deletePartialAssignment(String idPartialAssignment)
    {
        int rc=-1;

        String query="DELETE FROM partialAssignment WHERE idPartialAssignment = '" +
                idPartialAssignment + "'";
        System.out.println(query);
        try{
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            rc = stmt.executeUpdate(query);
            stmt.close();
        }
        catch(Exception ex){
            System.out.println("Delete exception in partialAssignment db: "+ex);
        }
        return(rc);
    }

    /**
     * Implementation of the private methods used in the above public methods is from here and downwards.
     */

    private ArrayList<PartialAssignment> miscWhere(String wClause)
    {
        ResultSet results;
        ArrayList<PartialAssignment> list = new ArrayList<PartialAssignment>();

        String query = buildQuery(wClause);

        try{
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            results = stmt.executeQuery(query);

            while (results.next()){
                PartialAssignment partObj = new PartialAssignment();
                partObj = buildPartialAssignment(results);
                list.add(partObj);
            }
            stmt.close();

                /*
                if (retrieveAssociation){
                    for (Order orderObj : list){

                    }
                }
                */
        }
        catch (Exception e){
            System.out.println("Query exception - select: " + e);
            e.printStackTrace();
        }
        return list;
    }

    private PartialAssignment singleWhere(String wClause){
        ResultSet results;
        PartialAssignment partObj = new PartialAssignment();

        String query = buildQuery(wClause);
        System.out.println(query);

        try {
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            results = stmt.executeQuery(query);

            if (results.next()){
                partObj = buildPartialAssignment(results);

                stmt.close();
                /*
                if (retrieveAssociation)
                {

                }
                */
            }
        }
        catch(Exception e){
            System.out.println("Query exception: " + e);
        }
        return partObj;
    }

    private String buildQuery(String wClause){
        String query="SELECT idPartialAssignment, nameAssignment, " +
                "area, hourExpected, hourSpent, note, priceAssignment FROM partialAssignment";

        if (wClause.length() > 0)
            query = query+" WHERE "+wClause;

        return query;
    }

    private PartialAssignment buildPartialAssignment(ResultSet results){
        PartialAssignment partObj = new PartialAssignment();
        try{
            partObj.setIdPartialAssignment(results.getString("idPartialAssignment"));
            //partObj.setOffer(new Offer(results.getString("idOffer")));
            //partObj.setMaterialList();
            //partObj.setEmployee(new Employee(results.getString("idEmployee")));
            //partObj.setCustomer(new Customer(results.getString("idCustomer")));
            partObj.setNameAssignment(results.getString("nameAssignment"));
            partObj.setArea(results.getDouble("area"));
            partObj.setHourExpected(results.getInt("hourExpected"));
            partObj.setHourSpent(results.getInt("hourSpent"));
            partObj.setNote(results.getString("note"));
            partObj.setPriceAssignment(results.getDouble("priceAssignment"));

        }
        catch (Exception e){
            System.out.println("error is building the partialAssignment object");
        }
        return partObj;
    }
}